package com.pmcc.platform.shopping.repository;

import com.pmcc.platform.shopping.entity.GoodsGift;
import org.omg.CosNaming.NamingContextExtPackage.StringNameHelper;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.security.access.method.P;

import java.util.Date;
import java.util.LinkedList;
import java.util.List;

public interface GoodsGiftRepository extends JpaRepository<GoodsGift, String> {

    GoodsGift findAllByIdAndDelFlag(String id, String delFlag);

    List<GoodsGift> findAllByGoodsIdAndDelFlag(String goodId, String delFlag);

    Page<GoodsGift> findAllByGoodsIdAndShoppingIdAndShopIdContainsAndDelFlagAndStateAndCreateTimeBetweenOrderByCreateTimeDesc(String goodId, String shoppingId, String shopId, String delFlag, String state, Date startTime, Date endTime, Pageable pageable);


    @Query(value = "SELECT count(1) FROM `bp_goods_gift` where goods_id=?1 and state=?2 and del_flag=?3", nativeQuery = true)
    int countGoodNum(String goodId, String state, String delFlag);

    @Query(value = "SELECT count(1) FROM `bp_goods_gift` where shop_id=?1 and state=?2 and del_flag=?3", nativeQuery = true)
    int countShopNum(String shopId, String state, String delFlag);

    @Query(value = "SELECT count(1) FROM `bp_goods_gift` where shopping_id=?1 and state=?2 and del_flag=?3", nativeQuery = true)
    int countShoppingNum(String shopId, String state, String delFlag);

    @Query(value = "SELECT count(1) FROM `bp_goods_gift` where salesman_id=?1 and state=?2 and del_flag=?3", nativeQuery = true)
    int countSalesmanNum(String salesman, String state, String delFlag);

    @Query(value = "SELECT count(1) FROM `bp_goods_gift` where user_open_id=?1 and state=?2 and del_flag=?3", nativeQuery = true)
    int countUserNum(String userOpenId, String state, String delFlag);

    Page<GoodsGift> findAllByUserOpenIdAndDelFlagAndState(String userOpenID, String delFLag, String state, Pageable pageable);

    int countAllByDelFlagAndUserOpenIdAndState(String delFlag, String openId, String state);


    Page<GoodsGift> findAllByShoppingIdAndDelFlagAndStateAndShopIdContainsAndCreateTimeBetweenOrderByModifyName(String shoppingId, String delFlag, String state, String shopId, Date startTime, Date endTime, Pageable pageable);


    int countAllByUserOpenIdAndDelFlagAndState(String userOpenId, String delFlag, String state);

    int countAllByUserOpenIdAndDelFlagAndStateAndSalesmanId(String openId,String delFlag,String state,String salesmanId);

    @Query(value = "SELECT DISTINCT(user_open_id) FROM `bp_goods_gift` where state=?1 AND del_flag=?2 AND shop_id=?3 LIMIT ?4,?5", nativeQuery = true)
    List<String> findDistinctByUserOpenIdAndDelFlagAndState(String state, String delFlag, String shopId, int a, int b);

    @Query(value = "SELECT DISTINCT(user_open_id) FROM `bp_goods_gift` where state=?1 AND del_flag=?2 AND shop_id=?3 ", nativeQuery = true)
    List<String> findDistinctByUserOpenId(String state, String delFlag, String shopId);

    @Query(value = "SELECT count(1) FROM `bp_goods_gift` where shop_id=?1 and goods_id=?2 and state=?3 and del_flag=?4", nativeQuery = true)
    int countshopandgoodNum(String shopId, String goodsId, String state, String delFlag);


    @Query(value = "SELECT DISTINCT(user_open_id) FROM `bp_goods_gift` where shop_id=?1 and goods_id=?2 and state=?3 and del_flag=?4 and shopping_id=?5 and create_time BETWEEN  ?6 and  ?7", nativeQuery = true)
    List<String> countshopandgood(String shopId, String goodsId, String state, String delFlag, String shoppingId, Date startTime, Date endTime);

    @Query(value = "SELECT count(1) FROM `bp_goods_gift` where shop_id=?1 and goods_id=?2 and state=?3 and del_flag=?4 and shopping_id=?5", nativeQuery = true)
    int countshopandgoodNumandShoppingId(String shopId, String goodsId, String state, String delFlag, String shoppingId);

    List<GoodsGift> findAllByShopIdAndShoppingIdAndDelFlagAndStateAndGoodsIdAndCreateTimeBetween(String shopId, String shoppingId, String delFlag, String state,String ggodsId, Date startTime, Date endTime);

    @Query(value = "SELECT count(1) FROM `bp_goods_gift` where shop_id=?1  and state=?2 and del_flag=?3 and shopping_id=?4", nativeQuery = true)
    int countDistinctByGoodsId(String shopId, String state, String delFlag, String shoppingId);

    @Query(value = "SELECT count(1) FROM `bp_goods_gift` where shop_id=?1 and state=?2 and del_flag=?3", nativeQuery = true)
    int countshop(String shopId, String state, String delFlag);


    @Query(value = "SELECT DISTINCT(user_open_id) FROM `bp_goods_gift` where state=?1 AND del_flag=?2 AND salesman_id=?3 LIMIT ?4,?5", nativeQuery = true)
    List<String> findDistinctByUserOpenId(String state, String delFlag, String salesmanId, int a, int b);

    @Query(value = "SELECT DISTINCT(user_open_id) FROM `bp_goods_gift` where state=?1 AND del_flag=?2 AND salesman_id=?3", nativeQuery = true)
    List<String> findAllDistinctByUserOpenId(String state, String delFlag, String salesmanId);

    Page<GoodsGift> findAllBySalesmanIdAndUserOpenIdAndDelFlagAndState(String salesmanId,String userOpenId,String delFlag,String state, Pageable pageable);

    int countAllBySalesmanIdAndDelFlagAndStateAndGoodsId(String salesmanId,String delFlag,String state,String goodsId);

    Page<GoodsGift> findAllBySalesmanIdAndGoodsIdAndStateAndDelFlagOrderByCreateTimeDesc(String salesmanId,String goodsId,String state,String delFlag, Pageable pageable);

    List<GoodsGift> findAllByShopIdAndGoodsIdAndDelFlagAndState(String shopid,String goodsId,String delFlag,String state);

    @Query(value = "SELECT DISTINCT(shopping_id) FROM `bp_goods_gift` where state=?1 AND del_flag=?2 AND shop_id=?3 AND goods_id=?4", nativeQuery = true)
    List<String> findAllDistinctByUShoppingId(String state, String delFlag, String shopId,String goodsId);

    int countAllByShoppingIdAndDelFlagAndStateAndGoodsId(String shoppingId,String delFlag,String state,String goodsId);

    int countAllBySalesmanIdAndDelFlagAndStateAndGoodsIdAndShopIdContains(String salesmanId,String delFlag,String state,String goodsId,String shopId);
}
